﻿using ASmile.CodeFactory.DBModel;
using ASmile.Extend;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ASmile.CodeFactory.DaBLL.Builder
{
    class BuilderSqlServer : IBuilder
    {
        public BuilderSqlServer(TConfig config)
            : base(config)
        { }

        protected override DataTable GetTableSchema()
        {
            return GetSchema("Table_Name", "Tables", null, null, null, "BASE TABLE");
        }

        class M_TableRemark
        { 
            public string TableName { set; get; }

            public string TableRemark { set; get; }
        }

        protected override Dictionary<string, string> GetDbTableRemarks()
        {
            var rDict = new Dictionary<string, string>();

            var sql = DaBLL.CreateSqlBuilder();
            sql.AddSQLParam(@"
select 
  a.name AS TableName,
  isnull(g.[value],'') AS TableRemark
from sys.tables a 
left join sys.extended_propertIEs g  on (a.object_ID = g.major_ID AND g.minor_ID = 0)
where a.name not in('sysdiagrams','ASPStateTempSessions','ASPStateTempApplications')
order by TableName");
            var rList = DaBLL.Query(sql).ToList<M_TableRemark>();
            if (rList != null)
            {
                foreach (var item in rList)
                {
                    rDict[item.TableName] = item.TableRemark;
                }
            }
            return rDict;
        }

        class M_Primarykey
        {
            public string PkColName { set; get; }
        }

        protected override List<string> GetPrimarykeys(string tableName)
        {
            var sql = DaBLL.CreateSqlBuilder();
            sql.AddSQLParam(@"
SELECT 
	t.name as PkColName
FROM SYSCOLUMNS t
inner join SYSOBJECTS a on a.parent_obj = t.id and a.xtype = 'PK' 
inner join SYSINDEXES b on b.id = t.id and a.name = b.name 
inner join SYSINDEXKEYS c on c.id = t.id and t.colid = c.colid and b.indid = c.indid
WHERE t.id = object_id({0})", tableName);
            var pkList = DaBLL.Query(sql).ToList<M_Primarykey>();
            return pkList?.Select(s => s.PkColName).ToList();

        }

        class M_ColRemark
        {
            public string ColName { set; get; }

            public string ColRemark { set; get; }
        }

        protected override Dictionary<string, string> GetDbFieldRemarks(string tbName)
        {
            var rDict = new Dictionary<string, string>();

            var sql = DaBLL.CreateSqlBuilder();
            sql.AddSQLParam(@"
SELECT
	d.[name] as TableName,
	a.[colorder] as ColOrder,
	a.[name] as ColName,
	g.[value] AS ColRemark
FROM syscolumns a 
	inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
	left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id 
WHERE d.[name] ={0}", tbName);
            var rList = DaBLL.Query(sql).ToList<M_ColRemark>();
            if (rList != null)
            {
                foreach (var item in rList)
                {
                    if (!rDict.TryGetValue(item.ColName, out string remark) && remark.IsNotEmpty())
                    {
                        continue;
                    }
                    rDict[item.ColName] = item.ColRemark;
                }
            }
            return rDict;
        }
    }
}